Comparison Operators

Not all comparison operators will permit Oracle to use an index.

Comparison Operators that will use and index

Comparison Operators that will not use and index

Comparison Operators that will use a Hash Cluster Scan

Only the Equals (=) and IN operators will allow a hash cluster scan. Unlike indexes, Range comparisons cannot be used with a cluster scan.

For regular b-Tree indexes, these rules are hard and fast; there are no exceptions (Bitmap and Bitmap-Join indexes work differently). Note that the comparison operators that do not use an index will typically return more rows than they filter out, so even if they could use an index, a full table scan would still be more efficient.

There are some rare examples where the ability to index on one of these comparison operators would be beneficial. For example, consider a cheque printing system where payments are entered throughout the day, populating the CHEQUE_NUMBER column with NULL. Overnight, a batch job selects all rows WHERE cheque_number IS NULL, and populates the column. In this example, we could create a function based index as follows:

CREATE INDEX payment_tbl_i1
ON payment_tbl(
   decode(cheque_number, NULL, 0, NULL)
)

Note that a CHEQUE_NUMBER of NULL is the only value we are interested in: all other values are decoded to NULL. Since Oracle does not store NULL values in the index, this index becomes particularly efficient and practical because it only stores values for a small percentage of the table. In our overnight cheque printing program, we select WHERE decode(cheque_number, NULL, 0, NULL) = 0, giving us indexed access to the rows we need.

Function based indexes - used creatively - can solve many indexing problems, but be warned: extra indexes take up space and make inserts slower. In particular, function based indexes are typically used for just one program or process, so the performance benefit to that process must be weighed against the performance loss to inserts and the extra cost of disk